"""add venue missing fields

Revision ID: 20251031_add_venue_fields
Revises: 20251031_add_user_role
Create Date: 2025-10-31 19:05:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '20251031_add_venue_fields'
down_revision = '20251031_add_user_role'
branch_labels = None
depends_on = None


def upgrade():
    """添加场地表缺失的字段"""
    from sqlalchemy import inspect
    from alembic import op
    
    # 获取数据库连接
    conn = op.get_bind()
    inspector = inspect(conn)
    
    # 获取现有列
    existing_columns = [col['name'] for col in inspector.get_columns('venues')]
    print(f"📋 现有字段: {existing_columns}")
    
    # 定义需要添加的字段（按顺序）
    fields_to_add = [
        ('contact_phone', "VARCHAR(20)", "address"),
        ('city_name', "VARCHAR(100)", "city_id"),
        ('price', "FLOAT", "contact_phone"),
        ('price_range', "VARCHAR(100)", "price"),
        ('price_note', "VARCHAR(500)", "price_range"),
        ('rating', "FLOAT DEFAULT 0", "price_note"),
        ('review_count', "INTEGER DEFAULT 0", "rating"),
        ('grass_type', "VARCHAR(50)", "review_count"),
        ('facilities', "TEXT", "grass_type"),
        ('images', "TEXT", "facilities"),
        ('business_hours', "TEXT", "images"),
        ('description', "TEXT", "business_hours"),
        ('has_parking', "TINYINT(1) DEFAULT 0", "description"),
        ('has_shower', "TINYINT(1) DEFAULT 0", "has_parking"),
        ('has_cover', "TINYINT(1) DEFAULT 0", "has_shower"),
        ('has_light', "TINYINT(1) DEFAULT 0", "has_cover"),
        ('status', "VARCHAR(20) DEFAULT 'available'", "has_light"),
        ('manager_id', "VARCHAR(36)", "status"),
        ('creator_type', "VARCHAR(50)", "manager_id"),
        ('creator_id', "VARCHAR(36)", "creator_type"),
        ('city_manager_id', "VARCHAR(36)", "creator_id"),
        ('audit_status', "VARCHAR(20) DEFAULT 'pending'", "city_manager_id"),
        ('audit_note', "TEXT", "audit_status"),
        ('audited_by', "VARCHAR(36)", "audit_note"),
        ('audited_at', "DATETIME", "audited_by"),
        ('is_recommended', "TINYINT(1) DEFAULT 0", "audited_at"),
        ('sort_order', "INTEGER DEFAULT 0", "is_recommended"),
        ('view_count', "INTEGER DEFAULT 0", "sort_order"),
        ('favorite_count', "INTEGER DEFAULT 0", "view_count"),
    ]
    
    # 逐个添加字段
    for field_name, field_type, after_column in fields_to_add:
        if field_name not in existing_columns:
            try:
                sql = f"ALTER TABLE venues ADD COLUMN {field_name} {field_type} AFTER {after_column}"
                print(f"✅ 添加字段: {field_name}")
                op.execute(sql)
            except Exception as e:
                print(f"⚠️  添加字段 {field_name} 失败: {e}")
                # 如果 AFTER 子句失败，尝试直接添加
                try:
                    sql = f"ALTER TABLE venues ADD COLUMN {field_name} {field_type}"
                    op.execute(sql)
                    print(f"✅ 添加字段成功（无 AFTER）: {field_name}")
                except Exception as e2:
                    print(f"❌ 完全失败: {field_name} - {e2}")
        else:
            print(f"⏭️  字段已存在: {field_name}")
    
    # 获取现有索引
    existing_indexes = [idx['name'] for idx in inspector.get_indexes('venues')]
    
    # 添加索引（如果不存在）
    if 'ix_venues_city_name' not in existing_indexes:
        try:
            op.execute("CREATE INDEX ix_venues_city_name ON venues(city_name)")
            print("✅ 索引创建成功: ix_venues_city_name")
        except Exception as e:
            print(f"⚠️  索引创建失败: {e}")


def downgrade():
    """回滚：删除添加的字段"""
    from sqlalchemy import inspect
    
    # 获取数据库连接
    conn = op.get_bind()
    inspector = inspect(conn)
    
    # 获取现有索引
    existing_indexes = [idx['name'] for idx in inspector.get_indexes('venues')]
    
    # 删除索引（MySQL 不支持 IF EXISTS）
    if 'ix_venues_city_name' in existing_indexes:
        try:
            op.execute("DROP INDEX ix_venues_city_name ON venues")
            print("✅ 索引删除成功: ix_venues_city_name")
        except Exception as e:
            print(f"⚠️  索引删除失败: {e}")
    
    # 获取现有列
    existing_columns = [col['name'] for col in inspector.get_columns('venues')]
    
    # 定义需要删除的字段（按添加的逆序）
    columns_to_drop = [
        'favorite_count', 'view_count', 'sort_order', 'is_recommended',
        'audited_at', 'audited_by', 'audit_note', 'audit_status',
        'city_manager_id', 'creator_id', 'creator_type', 'manager_id',
        'status', 'has_light', 'has_cover', 'has_shower', 'has_parking',
        'description', 'business_hours', 'images', 'facilities', 'grass_type',
        'review_count', 'rating', 'price_note', 'price_range', 'price',
        'city_name', 'contact_phone'
    ]
    
    # 逐个删除字段
    for column in columns_to_drop:
        if column in existing_columns:
            try:
                op.execute(f"ALTER TABLE venues DROP COLUMN {column}")
                print(f"✅ 删除字段: {column}")
            except Exception as e:
                print(f"⚠️  删除字段失败 {column}: {e}")

